
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/20/2011 16:26:51
-- Generated from EDMX file: D:\Codeplex\EzDesk-Dev\Services\Apps\EzDesk.Apps.Data\ProjectModule\EzDeskProject.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [EzDeskProject];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ProjectMemberInProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MemberInProjectSet] DROP CONSTRAINT [FK_ProjectMemberInProject];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProjectSet] DROP CONSTRAINT [FK_ProjectProject];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectProjectStatus]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProjectSet] DROP CONSTRAINT [FK_ProjectProjectStatus];
GO
IF OBJECT_ID(N'[dbo].[FK_WorkitemProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkitemSet] DROP CONSTRAINT [FK_WorkitemProject];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[WorkitemSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkitemSet];
GO
IF OBJECT_ID(N'[dbo].[DailyWorksSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DailyWorksSet];
GO
IF OBJECT_ID(N'[dbo].[ProjectSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProjectSet];
GO
IF OBJECT_ID(N'[dbo].[MemberInProjectSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MemberInProjectSet];
GO
IF OBJECT_ID(N'[dbo].[ProjectStatusSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProjectStatusSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'WorkitemSet'
CREATE TABLE [dbo].[WorkitemSet] (
    [Id] uniqueidentifier  NOT NULL,
    [OwnerId] uniqueidentifier  NOT NULL,
    [CreatorId] uniqueidentifier  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [IsFinished] bit  NOT NULL,
    [FinishOn] datetime  NULL,
    [Points] int  NOT NULL,
    [ProjectId] uniqueidentifier  NULL
);
GO

-- Creating table 'DailyWorksSet'
CREATE TABLE [dbo].[DailyWorksSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [Day] datetime  NULL,
    [DailyWork] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ProjectSet'
CREATE TABLE [dbo].[ProjectSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Code] nvarchar(max)  NOT NULL,
    [ChargePersonId] uniqueidentifier  NULL,
    [Description] nvarchar(max)  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [MemberString] nvarchar(max)  NOT NULL,
    [PlanOn] datetime  NULL,
    [FinishOn] datetime  NULL,
    [BeginOn] datetime  NULL,
    [ParentProject_Id] uniqueidentifier  NULL,
    [ProjectStatus_Id] int  NULL
);
GO

-- Creating table 'MemberInProjectSet'
CREATE TABLE [dbo].[MemberInProjectSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [ProjectId] uniqueidentifier  NULL
);
GO

-- Creating table 'ProjectStatusSet'
CREATE TABLE [dbo].[ProjectStatusSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StatusName] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'WorkitemSet'
ALTER TABLE [dbo].[WorkitemSet]
ADD CONSTRAINT [PK_WorkitemSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DailyWorksSet'
ALTER TABLE [dbo].[DailyWorksSet]
ADD CONSTRAINT [PK_DailyWorksSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProjectSet'
ALTER TABLE [dbo].[ProjectSet]
ADD CONSTRAINT [PK_ProjectSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MemberInProjectSet'
ALTER TABLE [dbo].[MemberInProjectSet]
ADD CONSTRAINT [PK_MemberInProjectSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProjectStatusSet'
ALTER TABLE [dbo].[ProjectStatusSet]
ADD CONSTRAINT [PK_ProjectStatusSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ProjectId] in table 'MemberInProjectSet'
ALTER TABLE [dbo].[MemberInProjectSet]
ADD CONSTRAINT [FK_ProjectMemberInProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[ProjectSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectMemberInProject'
CREATE INDEX [IX_FK_ProjectMemberInProject]
ON [dbo].[MemberInProjectSet]
    ([ProjectId]);
GO

-- Creating foreign key on [ParentProject_Id] in table 'ProjectSet'
ALTER TABLE [dbo].[ProjectSet]
ADD CONSTRAINT [FK_ProjectProject]
    FOREIGN KEY ([ParentProject_Id])
    REFERENCES [dbo].[ProjectSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectProject'
CREATE INDEX [IX_FK_ProjectProject]
ON [dbo].[ProjectSet]
    ([ParentProject_Id]);
GO

-- Creating foreign key on [ProjectStatus_Id] in table 'ProjectSet'
ALTER TABLE [dbo].[ProjectSet]
ADD CONSTRAINT [FK_ProjectProjectStatus]
    FOREIGN KEY ([ProjectStatus_Id])
    REFERENCES [dbo].[ProjectStatusSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectProjectStatus'
CREATE INDEX [IX_FK_ProjectProjectStatus]
ON [dbo].[ProjectSet]
    ([ProjectStatus_Id]);
GO

-- Creating foreign key on [ProjectId] in table 'WorkitemSet'
ALTER TABLE [dbo].[WorkitemSet]
ADD CONSTRAINT [FK_WorkitemProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[ProjectSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WorkitemProject'
CREATE INDEX [IX_FK_WorkitemProject]
ON [dbo].[WorkitemSet]
    ([ProjectId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------